--    Program: 		CAPPOIMP_ITEM_TL.sql
--    Description:  Import PO ITEM TL information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         27-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_inventory_item_id		number;
                    		                 		
   cursor cur_mtl_system_items_tl is
      select * from CRST_MTL_SYSTEM_ITEMS_TL;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_mtl_system_items_tl loop   	
     l_err_msg_tmp := '';
   	 
   	 l_inventory_item_id := null; 	
     begin
       SELECT inventory_item_id_n
       INTO   l_inventory_item_id
       FROM   CRST_MTL_SYSTEM_ITEMS_B
       WHERE  inventory_item_id = cur.INVENTORY_ITEM_ID;
     exception
       when others then
         l_err_msg_tmp := 'New Inventory ID '||cur.INVENTORY_ITEM_ID||' not found';
     end;  	  
     
     begin
       -- import data
       insert into MTL_SYSTEM_ITEMS_TL
       (
            INVENTORY_ITEM_ID  ,
            ORGANIZATION_ID    ,
            LANGUAGE           ,
            SOURCE_LANG        ,
            DESCRIPTION        ,
            LAST_UPDATE_DATE   ,
            LAST_UPDATED_BY    ,
            CREATION_DATE      ,
            CREATED_BY         ,
            LAST_UPDATE_LOGIN  ,
            LONG_DESCRIPTION   
       )
       values
       (
            l_inventory_item_id    ,
            CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORGANIZATION_ID),
            cur.LANGUAGE           ,
            cur.SOURCE_LANG        ,
            cur.DESCRIPTION        ,
            cur.LAST_UPDATE_DATE   ,
            cur.LAST_UPDATED_BY    ,
            cur.CREATION_DATE      ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN  ,
            cur.LONG_DESCRIPTION   
       );
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_ITEM_TL', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
